﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace eNew.ConsoleApps.ImportTxtToSQL.DAL
{
    using BusinessEntities;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Data;
    class SqlAccessLayer
    {

        public static void InsertImportData(ImportData rec)
        {
            //using (SqlConnection conn = new SqlConnection(appSetting.dbConnection))
            //{
            //    string insertSql = " INSERT INTO [dbo].[EFT] ( [AgentAgencyAccount] ,[PayeeName]  ,[Bank] ,[AccountNumber] ,[Type],[Amount],[EndingDate]) ";
            //    insertSql += " VALUES ( @AgentAgencyAccount, @PayeeName, @Bank,@AccountNumber, @Type,@Amount,@EndingDate ) ";

            //    SqlParameter[] parameters = { 
            //                                 new SqlParameter("@AgentAgencyAccount", data.AgentAgencyAccount),
            //                                 new SqlParameter ("@PayeeName",data.PayeeName),
            //                                 new SqlParameter ("@Bank",data.Bank ),
            //                                 new SqlParameter ("@AccountNumber",data.AccountNumber),
            //                                 new SqlParameter ("@Type",data.Type),
            //                                 new SqlParameter ("@Amount", Double.Parse(data.Amount) ) ,
            //                                 new SqlParameter ("@EndingDate",data.EndingDate)
                                           


            //                                };
            //    conn.Open();
            //    SqlCommand cmd = new SqlCommand(insertSql, conn);
            //    //cmd.Parameters.Add("@AgentAgencyAccount", SqlDbType.VarChar, 50).Value =  data.AgentAgencyAccount;
            //    cmd.Parameters.AddRange(parameters);
            //    cmd.ExecuteNonQuery();


            //}

            string sql = " INSERT INTO [DBCS].[dbo].[EFT] ( [AgencyAgentAccount] , [PayeeName], [Bank]   , [AccountNumber] ,[Type] , [Amount], [EndingDate] )  ";
            sql += " VALUES ( @AAAccount ,@Name,  ,@Bank, @AccountNumber ,@Type   ,@Amount,  @EndingDate ) ";
            //SqlParameter[] parm = {
            //                          new SqlParameter("@AAAccount", rec.AAAccount),
            //                          new SqlParameter("@Name", rec.Name),
            //                          new SqlParameter("@Bank", rec.Bank),
            //                          new SqlParameter("@AccountNumber", rec.Account),
            //                          new SqlParameter("@Type", rec.Type),
            //                          new SqlParameter("@Amount", rec.Amount),
            //                           new SqlParameter("@EndingDate", rec.EndDate)
            //                       };
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                 SqlCommand cmd = new SqlCommand(sql,con);

                 cmd.Parameters.Add("@AAAccount", SqlDbType.VarChar, 15).Value = rec.AAAccount;
                 cmd.Parameters.Add("@Name", SqlDbType.VarChar, 25).Value = rec.Name;
                 cmd.Parameters.Add("@Bank", SqlDbType.VarChar, 50).Value = rec.Bank;
                 cmd.Parameters.Add("@AccountNumber", SqlDbType.VarChar, 50).Value = rec.Account;
                 cmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = rec.Type;
                 cmd.Parameters.Add("@Amount", SqlDbType.Money).Value = double.Parse(rec.Amount);
                 cmd.Parameters.Add("@EndingDate", SqlDbType.SmallDateTime).Value = DateTime.Parse (rec.EndDate).ToShortDateString();
                 try
                 {
                     con.Open();
                     cmd.ExecuteNonQuery();
                 }
                 catch(SqlException ex)
                 {
                     Console.WriteLine(ex.ToString());

                 }
            }

        }

    }
}
